package kr.hkit.team.score.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Iterator;
import java.util.ListIterator;

import kr.hkit.team.score.dto.Score;

public class ScoreDao {
	private Connection connection;
	private PreparedStatement pstmt;

	public ScoreDao(Connection connection) {
		this.connection = connection;
	}

	/**
	 * stdNum: 학번 stdName: 이름 kor: 국어점수 eng: 영어점수 math: 수학점수 total: 총점 avg: 평균
	 */
	public ArrayList<Score> listItems() throws SQLException {
		ArrayList<Score> list = new ArrayList<>();
		String sql = "select s.stdNum, s.stdName, c.kor, c.eng, e.math "
				+ "from student s, score c where s.stdNum=p.stdNum";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			Score s = new Score();
			s.setStdNum(rs.getInt(1));
			s.setStdName(rs.getString(2));
			s.setKor(rs.getInt(3));
			s.setEng(rs.getInt(4));
			s.setMath(rs.getInt(5));
			s.setTotal();
			s.setAvg();
			list.add(s);
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}

	public ArrayList<Score> setrank() throws SQLException {
		ArrayList<Score> lists = listItems();
		Collections.sort(lists, new Comparator<Score>() {

			@Override
			public int compare(Score o1, Score o2) {
				return o1.getTotal() - o2.getTotal();
			}
		});

		for (int i = 1; i < lists.size(); i++) {
			lists.get(i).setRank(i+1);
			if(lists.get(i-1).getTotal() == lists.get(i).getTotal()){
				lists.get(i).setRank(lists.get(i-1).getRank());
			}else{
				lists.get(i).setRank(i+1);
			}
		}
		return lists;
	}
}
